﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_FIM_MAS_BankBranchs : IDAL_FIM_MAS_BankBranchs
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public D_FIM_MAS_BankBranchs()
        {

        }

        public IList<M_FIM_MAS_BankBranchs> GetFull(Nullable<int> bank_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MAS_BankBranchs> infoList = new List<M_FIM_MAS_BankBranchs>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bank_seq", bank_seq);

                strSQL = "SELECT bb.branch_seq, bb.branch_id, bb.branch_name, bb.branch_name_eng"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, b.bank_name_eng"
                    //+ ", bb.address, bb.district_seq, bb.country_seq"
                    //+ ", concat(d.district_name, ' | ', a.amp_name, ' | ', p.prov_name) as district_text"
                    //+ ", c.country_name"
                    + ", bb.record_status"
                    + ", bb.created_by, bb.created_when"
                    + ", bb.updated_by, bb.updated_when"
                    + " FROM fim_mas_bank_branchs bb"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    //+ " LEFT JOIN mas_districts d ON bb.district_seq = d.district_seq"
                    //+ " LEFT JOIN mas_amphurs a ON d.amp_seq = a.amp_seq"
                    //+ " LEFT JOIN mas_provinces p ON a.prov_seq = p.prov_seq"
                    //+ " LEFT JOIN mas_countries c ON bb.country_seq = c.country_seq"
                    + " WHERE bb.record_status not like @delete_status"
                    + (bank_seq != null ? " and bb.bank_seq = @bank_seq" : "")
                    + " ORDER BY b.bank_id, bb.branch_id";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MAS_BankBranchs info = new M_FIM_MAS_BankBranchs();
                        info.branch_seq = Convert.ToInt32(drc[i]["Branch_seq"]);
                        info.branch_id = drc[i]["Branch_id"].ToString();
                        info.branch_name = drc[i]["Branch_name"].ToString();
                        info.branch_name_eng = drc[i]["Branch_name_eng"].ToString();

                        info.bank_seq = Convert.ToInt32(drc[i]["Bank_seq"]);
                        info.bank_id = drc[i]["Bank_id"].ToString();
                        info.bank_name = drc[i]["Bank_name"].ToString();
                        info.bank_name_eng = drc[i]["Bank_name_eng"].ToString();

                        //info.address = drc[i]["Address"].ToString();
                        //info.district_seq = cn.ConvertNullInt(drc[i]["District_seq"]);
                        //info.country_seq = cn.ConvertNullInt(drc[i]["Country_seq"]);
                        //info.district_text = drc[i]["District_text"].ToString();
                        //info.country_name = drc[i]["Country_name"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MAS_BankBranchs> GetList(Nullable<int> bank_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MAS_BankBranchs> infoList = new List<M_FIM_MAS_BankBranchs>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bank_seq", bank_seq);

                strSQL = "SELECT bb.branch_seq, CONCAT(bb.branch_id, ' - ', bb.branch_name) AS branch_name"
                    + " FROM fim_mas_bank_branchs bb"
                    + " WHERE bb.record_status not like @delete_status"
                    + (bank_seq != null ? " and bb.bank_seq = @bank_seq" : "")
                    + " ORDER BY bb.branch_id";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MAS_BankBranchs info = new M_FIM_MAS_BankBranchs();
                        info.branch_seq = Convert.ToInt32(drc[i]["Branch_seq"]);
                        info.branch_name = drc[i]["Branch_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_MAS_BankBranchs GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MAS_BankBranchs info = new M_FIM_MAS_BankBranchs();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT bb.branch_seq, bb.branch_id, bb.branch_name, bb.branch_name_eng"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, b.bank_name_eng"
                    //+ ", bb.address, bb.district_seq, bb.country_seq"
                    //+ ", concat(d.district_name, ' ', a.amp_name, ' ', p.prov_name) as district_text"
                    //+ ", c.country_name"
                    + ", bb.record_status"
                    + ", bb.CREATED_BY, bb.CREATED_WHEN"
                    + ", bb.UPDATED_BY, bb.UPDATED_WHEN"
                    + " FROM fim_mas_bank_branchs bb"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    //+ " LEFT JOIN mas_districts d ON bb.district_seq = d.district_seq"
                    //+ " LEFT JOIN mas_amphurs a ON d.amp_seq = a.amp_seq"
                    //+ " LEFT JOIN mas_provinces p ON a.prov_seq = p.prov_seq"
                    //+ " LEFT JOIN mas_countries c ON bb.country_seq = c.country_seq"
                    + " WHERE bb.bank_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.branch_seq = Convert.ToInt32(drc[0]["Branch_seq"]);
                    info.branch_id = drc[0]["Branch_id"].ToString();
                    info.branch_name = drc[0]["Branch_name"].ToString();
                    info.branch_name_eng = drc[0]["Branch_name_eng"].ToString();

                    info.bank_seq = Convert.ToInt32(drc[0]["Bank_seq"]);
                    info.bank_id = drc[0]["Bank_id"].ToString();
                    info.bank_name = drc[0]["Bank_name"].ToString();
                    info.bank_name_eng = drc[0]["Bank_name_eng"].ToString();

                    //info.address = drc[0]["Address"].ToString();
                    //info.district_seq = cn.ConvertNullInt(drc[0]["District_seq"]);
                    //info.country_seq = cn.ConvertNullInt(drc[0]["Country_seq"]);
                    //info.district_text = drc[0]["District_text"].ToString();
                    //info.country_name = drc[0]["Country_name"].ToString();

                    info.record_status = drc[0]["RECORD_STATUS"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["CREATED_BY"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["CREATED_WHEN"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["UPDATED_BY"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["UPDATED_WHEN"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Insert(M_FIM_MAS_BankBranchs info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                //dbManager.AddParameters(0, "@Branch_seq", info.Branch_seq);
                dbManager.AddParameters(0, "@Branch_id", info.branch_id);
                dbManager.AddParameters(1, "@Branch_name", info.branch_name);
                dbManager.AddParameters(2, "@Branch_name_eng", info.branch_name_eng);
                dbManager.AddParameters(3, "@Bank_seq", info.bank_seq);
                dbManager.AddParameters(4, "@Address", "");
                dbManager.AddParameters(5, "@District_seq", "");
                dbManager.AddParameters(6, "@Country_seq", "");

                dbManager.AddParameters(7, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@created_by", info.created_by);
                dbManager.AddParameters(9, "@created_when", info.created_when);

                strSQL = "INSERT INTO fim_mas_bank_branchs"
                    + " (Branch_id, Branch_name, Branch_name_eng, Bank_seq"
                    //+ ", Address, District_seq, Country_seq"
                    + " , RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@Branch_id"
                    + ", @Branch_name"
                    + ", @Branch_name_eng"
                    + ", @Bank_seq"
                    //+ ", @Address"
                    //+ ", @District_seq"
                    //+ ", @Country_seq"

                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_MAS_BankBranchs info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@Branch_seq", info.branch_seq);
                dbManager.AddParameters(1, "@Branch_id", info.branch_id);
                dbManager.AddParameters(2, "@Branch_name", info.branch_name);
                dbManager.AddParameters(3, "@Branch_name_eng", info.branch_name_eng);
                dbManager.AddParameters(4, "@Bank_seq", info.bank_seq);
                dbManager.AddParameters(5, "@Address", "");
                dbManager.AddParameters(6, "@District_seq", "");
                dbManager.AddParameters(7, "@Country_seq", "");

                dbManager.AddParameters(8, "@updated_by", info.updated_by);
                dbManager.AddParameters(9, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_mas_bank_branchs"
                    + " SET Branch_id = @Branch_id"
                    + ", branch_name = @Branch_name"
                    + ", Branch_name_eng = @Branch_name_eng"
                    + ", Bank_seq = @Bank_seq"
                    //+ ", address = @Address"
                    //+ ", district_seq = @District_seq"
                    //+ ", country_seq = @Country_seq"
                    + ", updated_by = @Updated_by"
                    + ", updated_when = @Updated_when"
                    + " WHERE branch_seq = @Branch_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_MAS_BankBranchs info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Branch_seq", info.branch_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_mas_bank_branchs"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE Branch_seq = @Branch_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public DataTable SelectGrid()
        //{
        //    string strSQL;
        //    DataTable dtb = null;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "SELECT bb.branch_seq, bb.branch_id, bb.branch_name, bb.branch_name_eng"
        //            + ", bb.bank_seq, b.bank_id, b.bank_name, b.bank_name_eng, bb.address, bb.district_seq, bb.country_seq"
        //            + ", concat(d.district_name, ' ', a.amp_name, ' ', p.prov_name) as district_text"
        //            + ", c.country_name, bb.record_status"
        //            + " FROM fim_mas_bank_branchs bb"
        //            + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
        //            + " LEFT JOIN mas_districts d ON bb.district_seq = d.district_seq"
        //            + " LEFT JOIN mas_amphurs a ON d.amp_seq = a.amp_seq"
        //            + " LEFT JOIN mas_provinces p ON a.prov_seq = p.prov_seq"
        //            + " LEFT JOIN mas_countries c ON bb.country_seq = c.country_seq"
        //            + " WHERE bb.record_status not like 'D'"
        //            + " ORDER BY b.bank_id, bb.branch_id";
        //        

        //        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return dtb;
        //}

        //public DataTable SelectTreeView(string bank_seq)
        //{
        //    string strSQL;
        //    DataTable dtb = null;

        //    try
        //    {
        //        dbManager.Open();

        //        //strSQL = "SELECT concat('1', '-', cast(bank_seq as varchar)) as pk"
        //        //    + ", concat(bank_id, ' : ', bank_name) as name"
        //        //    + ", null as upper_pk"
        //        //    + " FROM fim_mas_banks"
        //        //    + " WHERE record_status not like 'D'"
        //        //    + " ORDER BY name";
        //        //dtb[0] = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

        //        //strSQL = "SELECT cast(branch_seq as varchar) as pk"
        //        //    + ", concat(branch_id, ' : ', branch_name) as name"
        //        //    + ", concat('1', '-', cast(bank_seq as varchar)) as upper_pk"
        //        //    + " FROM fim_mas_bank_branchs"
        //        //    + " WHERE record_status not like 'D'"
        //        //    + " ORDER BY name";

        //        strSQL = "SELECT branch_seq as pk"
        //            + ", concat(branch_id, ' : ', branch_name) as name"
        //            + ", bank_seq as upper_pk"
        //            + " FROM fim_mas_bank_branchs"
        //            + " WHERE record_status not like 'D'"
        //            + " AND bank_seq = " + Misc.ValueForSQL(bank_seq, Misc.DATA_TYPE_HINT.NUMERIC)
        //            + " ORDER BY name";

        //        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return dtb;
        //}

        //public void Insert(string branch_id, string branch_name, string branch_name_eng, string bank_seq
        //    , string address, string district_seq, string country_seq
        //    , string record_status, string created_by, DateTime created_when)
        //{
        //    string strSQL;

        //    strSQL = "INSERT INTO fim_mas_bank_branchs"
        //        + "(branch_id, branch_name, branch_name_eng, bank_seq"
        //            + ", address, district_seq, country_seq"
        //            + ", record_status, created_by, created_when)"
        //        + " VALUES (" + Misc.ValueForSQL(branch_id)
        //            + ", " + Misc.ValueForSQL(branch_name)
        //            + ", " + Misc.ValueForSQL(branch_name_eng)
        //            + ", " + Misc.ValueForSQL(bank_seq, Misc.DATA_TYPE_HINT.NUMERIC)
        //            + ", " + Misc.ValueForSQL(address)
        //            + ", " + Misc.ValueForSQL(district_seq, Misc.DATA_TYPE_HINT.NUMERIC)
        //            + ", " + Misc.ValueForSQL(country_seq, Misc.DATA_TYPE_HINT.NUMERIC)
        //            + ", " + Misc.ValueForSQL(record_status)
        //            + ", " + Misc.ValueForSQL(created_by, Misc.DATA_TYPE_HINT.NUMERIC)
        //            + ", " + Misc.ValueForSQL(created_when)
        //            + ")";
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}

        //public void Update(string branch_seq, string branch_id, string branch_name, string branch_name_eng
        //    , string bank_seq, string address, string district_seq, string country_seq
        //    , string updated_by, DateTime updated_when)
        //{
        //    string strSQL;

        //    strSQL = "UPDATE fim_mas_bank_branchs"
        //        + " SET branch_id = " + Misc.ValueForSQL(branch_id)
        //        + ", branch_name = " + Misc.ValueForSQL(branch_name)
        //        + ", branch_name_eng = " + Misc.ValueForSQL(branch_name_eng)
        //        + ", bank_seq = " + Misc.ValueForSQL(bank_seq, Misc.DATA_TYPE_HINT.NUMERIC)
        //        + ", address = " + Misc.ValueForSQL(address)
        //        + ", district_seq = " + Misc.ValueForSQL(district_seq, Misc.DATA_TYPE_HINT.NUMERIC)
        //        + ", country_seq = " + Misc.ValueForSQL(country_seq, Misc.DATA_TYPE_HINT.NUMERIC)
        //        + ", updated_by = " + Misc.ValueForSQL(updated_by, Misc.DATA_TYPE_HINT.NUMERIC)
        //        + ", updated_when = " + Misc.ValueForSQL(updated_when)
        //        + " WHERE branch_seq = " + Misc.ValueForSQL(branch_seq, Misc.DATA_TYPE_HINT.NUMERIC);

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}

        //public void Delete(string branch_seq, string updated_by, DateTime updated_when)
        //{
        //    string strSQL;

        //    strSQL = "UPDATE fim_mas_bank_branchs"
        //        + " SET record_status = 'D'"
        //        + ", updated_by = " + Misc.ValueForSQL(updated_by, Misc.DATA_TYPE_HINT.NUMERIC)
        //        + ", updated_when = " + Misc.ValueForSQL(updated_when)
        //        + " WHERE branch_seq = " + Misc.ValueForSQL(branch_seq, Misc.DATA_TYPE_HINT.NUMERIC);

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}
    }
}
